﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace Twi.DataAccess.Common
{
    /// <summary>
    /// 通用数据库访问类，封装了对数据库的常见操作
    /// </summary>
    public sealed class DbUtility
    {
        public string ConnectionString { get; set; }
        public DbProviderFactory providerFactory { get; set; }
        public DbProviderType _DbProviderType { get; set; }
        /// <summary>
        /// 参数前缀@ ：?
        /// </summary>
        public string ParameterPrefix { get; set; }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerType">数据库类型枚举，参见<paramref name="providerType"/></param>
        public DbUtility(string connectionString, DbProviderType providerType)
        {
            ConnectionString = connectionString;
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException("数据连接字符串没有配置正确：connectionString为空！");
            }
            _DbProviderType = providerType;
            ParameterPrefix = GetParameterPrefix();
            providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }
        }

        public DbUtility(string connectionString, string providerName)
        {
            ConnectionString = connectionString;
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException("数据连接字符串没有配置正确：connectionString为空！");
            }
            try
            {
                //从全局程序集中查找
                providerFactory = DbProviderFactories.GetFactory(providerName);
                _DbProviderType = ProviderFactory.GetDbProviderType(providerName);
                ParameterPrefix = GetParameterPrefix();
            }
            catch (Exception e)
            {
                throw new ArgumentException("数据连接字符串配置错误：" + e.Message);
            }
            if (providerFactory == null)
            {
                throw new ArgumentException("数据连接字符串没有配置providerName属性：");
            }
        }

        private string GetParameterPrefix()
        {
            if (_DbProviderType == DbProviderType.Oracle || _DbProviderType == DbProviderType.OracleDataAccess)
            {
                return ":";
            }
            else
            {
                return "@";
            }
        }

        /// <summary>   
        /// 执行SQL语句，返回影响的记录数  
        /// </summary>   
        /// <param name="sql">要执行的的SQL语句</param>   
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, IList<DbParameter> parameters = null, CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    command.Connection.Open();
                    int affectedRows = command.ExecuteNonQuery();
                    command.Connection.Close();
                    return affectedRows;
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }

            }
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个关联的DataReader实例   
        /// </summary>   
        /// <param name="sql">要执行的的SQL语句</param>   
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns> 
        public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters = null, CommandType commandType = CommandType.Text)
        {
            //TwiDAHelper.WriteLog(sql, parameters); //这个不需要写日志，因为调用的方法会写
            DbCommand command = CreateDbCommand(sql, parameters, commandType);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的的SQL语句</param>   
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters = null, CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;
                        DataTable data = new DataTable();
                        adapter.Fill(data);
                        return data;
                    }
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }
            }
        }

        /// <summary>   
        /// 执行查询语句，返回DataSet  
        /// </summary>   
        /// <param name="sql">要执行的的SQL语句</param>   
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, IList<DbParameter> parameters = null, CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }
            }
        }


        /// <summary>   
        /// 执行一个查询语句，返回查询结果的第一行第一列   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns>   
        public Object ExecuteScalar(string sql, IList<DbParameter> parameters = null, CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    command.Connection.Open();
                    object obj = command.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }

            }
        }

        /// <summary>
        /// 获取数据列表（返回XML对象）
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="nodeName">XML节点名称</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns>
        public XElement ExecuteXElement(string sql, IList<DbParameter> parameters = null, string nodeName = "Item", CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            XElement xeMultiple = new XElement(string.Format("{0}List", nodeName));
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    command.Connection.Open();
                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            XElement xe = new XElement(nodeName);
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (reader.GetDataTypeName(i) == "XmlType")
                                {
                                    xe.Add(ToXElement(reader, command, i, true));
                                }
                                else
                                {
                                    xe.Add(ToXElement(reader, command, i));
                                }
                            }
                            xeMultiple.Add(xe);
                        }
                    }
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }
            }
            return xeMultiple;
        }

        /// <summary>
        /// 获得单条数据(返回XML对象)
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="nodeName">XML节点名称</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns></returns>
        public XElement GetXElementSingleOrEmpty(string sql, IList<DbParameter> parameters = null, string nodeName = "Item", CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            XElement xe = new XElement(nodeName);
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    command.Connection.Open();
                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (reader.GetDataTypeName(i) == "XmlType")
                                {
                                    xe.Add(ToXElement(reader, command, i, true));
                                }
                                else
                                {
                                    xe.Add(ToXElement(reader, command, i));
                                }
                            }
                        }
                        else
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                xe.Add(new XElement(reader.GetName(i)));
                            }
                        }
                    }
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }
            }
            return xe;
        }


        /// <summary>
        /// 获得单条数据(返回XML对象)
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">[可选参数]参数</param>
        /// <param name="nodeName">XML节点名称</param>
        /// <param name="commandType">[可选参数]执行的SQL语句的类型</param>
        /// <returns>查询到记录返回，没有找到记录返回null</returns>
        public XElement GetXElementSingleOrDefault(string sql, IList<DbParameter> parameters = null, string nodeName = "Item", CommandType commandType = CommandType.Text)
        {
            TwiDAHelper.WriteLog(sql, parameters);
            XElement xe = new XElement(nodeName);
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                try
                {
                    command.Connection.Open();
                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (reader.GetDataTypeName(i) == "XmlType")
                                {
                                    xe.Add(ToXElement(reader, command, i, true));
                                }
                                else
                                {
                                    xe.Add(ToXElement(reader, command, i));
                                }
                            }
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
                catch (System.Exception ex)
                {
                    //command.Connection.Close();
                    throw ex;
                }
                finally
                {
                    if (command.Connection != null) command.Connection.Close();
                }
            }
            return xe;
        }


        /// <summary>
        /// 获得DbDataReader简析出XML结构
        /// </summary>
        /// <param name="rdr"></param>
        /// <param name="i"></param>
        /// <param name="isXml"></param>
        /// <returns></returns>
        private XElement ToXElement(DbDataReader rdr, DbCommand cmd, int i, bool isXml = false)
        {
            string name = rdr.GetName(i);
            if (rdr.GetValue(i) == DBNull.Value)
            {
                XElement xe = new XElement(name);
                return xe;
            }
            if (isXml)
            {
                return GetXElementGenerically(rdr, i);
            }
            else if (rdr.GetDataTypeName(i).ToUpper() == "RAW")
            {
                XElement xe = new XElement(name);
                xe.SetValue(new Guid(rdr.GetValue(i) as byte[]));
                return xe;
            }
            else
            {
                XElement xe = new XElement(name);
                xe.SetValue(ReplaceLowOrderASCIICharacters("" + rdr.GetValue(i)));
                return xe;
            }
        }


        private XElement GetXElementGenerically(DbDataReader rdr, int ordinal)
        {
            if (_DbProviderType == DbProviderType.Oracle || _DbProviderType == DbProviderType.OracleDataAccess)
            {
                return new XElement(rdr.GetName(ordinal), XElement.Parse(rdr.GetString(ordinal)).Elements());
            }
            else
            {
                try
                {
                    return XElement.Parse(string.Format("<{0}>", rdr.GetName(ordinal)) + rdr.GetString(ordinal) + string.Format("</{0}>", rdr.GetName(ordinal)));
                }
                catch
                {
                    return null;
                }
            }
        }

        private string ReplaceLowOrderASCIICharacters(string tmp)
        {
            StringBuilder info = new StringBuilder();
            foreach (char cc in tmp)
            {
                int ss = (int)cc;
                if (((ss >= 0) && (ss <= 8)) || ((ss >= 11) && (ss <= 12)) || ((ss >= 14) && (ss <= 32)))
                    info.AppendFormat(" ", ss);
                else info.Append(cc);
            }
            return info.ToString();
        }

        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public bool Exists(string strSql, IList<DbParameter> parameters = null, CommandType commandType = CommandType.Text)
        {
            object obj = ExecuteScalar(strSql, parameters, commandType);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse("" + obj);
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务
        /// </summary>
        /// <param name="tmps"></param>
        public void ExecuteSqlTransMultiPara(IList<TransMultiPara> tmps)
        {
            using (DbConnection connection = providerFactory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                using (DbTransaction trans = connection.BeginTransaction())
                {
                    using (DbCommand command = providerFactory.CreateCommand())
                    {
                        try
                        {
                            command.Transaction = trans;
                            command.Connection = connection;
                            foreach (TransMultiPara tmp in tmps)
                            {
                                command.CommandText = tmp.cmdText;
                                command.CommandType = tmp.commandType;
                                var parameters = tmp.cmdParams;
                                if (!(parameters == null || parameters.Count == 0))
                                {
                                    foreach (DbParameter parameter in parameters)
                                    {
                                        command.Parameters.Add(parameter);
                                    }
                                }
                                command.ExecuteNonQuery();
                                command.Parameters.Clear();
                            }
                            trans.Commit();
                        }
                        catch (System.Exception ex)
                        {
                            trans.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            if (command.Connection != null) command.Connection.Close();
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="action"></param>
        /// <returns>事务是否执行成功</returns>
        public bool ExecuteTransaction(Action<DbCommand> action)
        {
            bool isSuccess = false;
            using (DbConnection connection = providerFactory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                using (DbTransaction trans = connection.BeginTransaction())
                {
                    using (DbCommand command = providerFactory.CreateCommand())
                    {
                        try
                        {
                            command.Transaction = trans;
                            command.Connection = connection;
                            command.CommandType = CommandType.Text;

                            //执行自定义方法
                            action(command);

                            trans.Commit();
                            isSuccess = true;
                        }
                        catch (System.Exception ex)
                        {
                            trans.Rollback();
                            throw ex;

                        }
                        finally
                        {
                            if (command.Connection != null) command.Connection.Close();
                        }
                    }
                }
            }
            return isSuccess;
        }

        /// <summary>
        ///  查询多个实体集合
        /// </summary>
        /// <typeparam name="T">返回的实体集合类型</typeparam>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <remarks>[xzh]尽量不要使用返回实体的，这个需要转换，效率低，用返回DataTable代替</remarks>
        /// <returns></returns>
        public List<T> QueryForList<T>(string sql, IList<DbParameter> parameters, CommandType commandType = CommandType.Text) where T : new()
        {
            DataTable data = ExecuteDataTable(sql, parameters, commandType);
            return EntityReader.GetEntities<T>(data);
        }


        /// <summary>
        /// 查询单个实体
        /// </summary>
        /// <typeparam name="T">返回的实体集合类型</typeparam>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <remarks>[xzh]尽量不要使用返回实体的，这个需要转换，效率低，用返回DataTable代替</remarks>
        /// <returns></returns>
        public T QueryForObject<T>(string sql, IList<DbParameter> parameters, CommandType commandType = CommandType.Text) where T : new()
        {
            return QueryForList<T>(sql, parameters, commandType)[0];
        }

        public DbParameter CreateDbParameter(string name, object value, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value == null ? "" : value;
            if (value is DateTime)
            {
                parameter.DbType = DbType.DateTime;
            }
            parameter.Direction = parameterDirection;
            return parameter;
        }
        public DbParameter CreateDbParameter(string name, object value, DbType dbType, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value == null ? "" : value; ;
            parameter.DbType = dbType;
            parameter.Direction = parameterDirection;
            return parameter;
        }

        /// <summary>
        /// 拷贝DbParameter,主要是为了解决OracleParameter冲突的问题
        /// </summary>
        /// <param name="para"></param>
        /// <remarks>dbParams 竟然不能共用，另一个 OracleParameterCollection 中已包含 OracleParameter</remarks>
        /// <returns></returns>
        public DbParameter CloneDbParameter(DbParameter para)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = para.ParameterName;
            parameter.Value = para.Value == null ? "" : para.Value;
            parameter.DbType = para.DbType;
            parameter.Direction = para.Direction;
            return parameter;
        }
        /// <summary>
        /// 根据字典生成DbParameter列表 
        /// </summary>
        /// <param name="dict">【参数字典】字典的key为DbParameter的ParameterName，key对应的value为DbParameter的值</param>
        /// <param name="addPrefix">特殊情况下需要添加额外的前缀【当字典的key中统一没有加@前缀，则可以设置该参数的值为@】</param>
        /// <returns></returns>
        public IList<DbParameter> CreateDbParameter<T>(Dictionary<string, T> dict, string addPrefix = "")
        {
            if (dict == null)
            {
                return null;
            }
            IList<DbParameter> paras = new List<DbParameter>();
            object obj = "";
            foreach (string key in dict.Keys)
            {
                DbParameter parameter = providerFactory.CreateParameter();
                parameter.ParameterName = addPrefix + key;
                parameter.Value = dict[key] == null ? obj : dict[key];
                if (parameter.Value is DateTime)
                {
                    parameter.DbType = DbType.DateTime;
                }
                parameter.Direction = ParameterDirection.Input;
                paras.Add(parameter);
            }
            return paras;
        }

        /// <summary>
        /// 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        private DbCommand CreateDbCommand(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            DbConnection connection = providerFactory.CreateConnection();
            DbCommand command = providerFactory.CreateCommand();
            connection.ConnectionString = ConnectionString;
            command.CommandText = sql;
            command.CommandType = commandType;
            command.Connection = connection;
            if (!(parameters == null || parameters.Count == 0))
            {
                foreach (DbParameter parameter in parameters)
                {
                    //command.Parameters.Add(parameter);
                    command.Parameters.Add(CloneDbParameter(parameter));
                }
            }
            return command;
        }
        public DbDataAdapter CreateDataAdapter()
        {
            return providerFactory.CreateDataAdapter();
        }

        #region 2014-04-10新增

        #region 获取并缓存数据库表列的元数据信息（whd新增）

        private DataSet _metaDataSet = new DataSet();

        /// <summary>
        /// 获取并缓存数据库表列的元数据信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetMetaDataTable(string tableName)
        {
            if (_DbProviderType == DbProviderType.SqlServer)
            {
                return GetMetaDataTableForSQLServer(tableName);
            }
            else if (_DbProviderType == DbProviderType.Oracle)
            {
                return GetMetaDataTableForOracle(tableName);
            }
            else if (_DbProviderType == DbProviderType.OracleDataAccess)
            {
                return GetMetaDataTableForOracle(tableName);
            }
            else
            {
                return new DataTable();
            }

        }

        public DataTable GetMetaDataTableForSQLServer(string tableName)
        {
            if (!_metaDataSet.Tables.Contains(tableName))
            {
                #region SQL Server
                string strSql = string.Format(@"SELECT  
  表名 = case when a.colorder=1 then d.name else '' end,
  字段序号 = a.colorder,
  字段名 = a.name,
  标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end,
  主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
  SELECT name FROM sysindexes WHERE indid in(
  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end,
  类型 = b.name,
  占用字节数 = a.length,
  长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
  小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
  允许空 = case when a.isnullable=1 then '1'else '0' end,
  默认值 = isnull(e.text,''),
  计算列 = iscomputed
FROM  syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
where  d.name='{0}'
order by  a.id,a.colorder", tableName);
                DataTable dt = ExecuteDataTable(strSql);
                dt.TableName = tableName;
                _metaDataSet.Tables.Add(dt);
                #endregion

            }
            return _metaDataSet.Tables[tableName];
        }

        public DataTable GetMetaDataTableForOracle(string tableName)
        {
            if (!_metaDataSet.Tables.Contains(tableName))
            {
                #region Oracle
                string strSql = string.Format(@"select TABLE_NAME as ""表名"",
       column_id as  ""字段序号"",
        column_name as ""字段名"",
      case when column_name in (select cu.column_name from user_cons_columns cu, user_constraints au 
where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name =user_tab_columns.TABLE_NAME) then '1' else '0' end as  ""主键"",
       DATA_TYPE as ""类型"",
       DATA_LENGTH as ""长度"",
       case when NULLABLE='Y'then '1'else '0'end as ""允许空"",
       DATA_DEFAULT as ""默认值""
from user_tab_columns where TABLE_NAME='{0}'", tableName.ToUpper());
                DataTable dt = ExecuteDataTable(strSql);
                dt.TableName = tableName;
                _metaDataSet.Tables.Add(dt);
                //sa.Fill(_metaDataSet, tableName);
                foreach (DataRow dr in _metaDataSet.Tables[tableName.ToUpper()].Rows)
                {
                    if (dr["默认值"] != DBNull.Value)
                    {
                        StringBuilder strBuilder = new StringBuilder();
                        strBuilder.Append(dr["默认值"]);
                    }
                }
                #endregion

            }
            return _metaDataSet.Tables[tableName];
        }
        #endregion

        #region 获取元数据—主键
        /// <summary>
        /// 获取元数据—主键
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string[] GetMetaDataTablePrimaryKeys(string tableName)
        {
            return GetMetaDataTable(tableName).Rows.OfType<DataRow>().Where(x => x["主键"].ToString() == "1").Select(x => x["字段名"].ToString()).ToArray();
        }
        #endregion
        #region 获取系统参数
        private DataTable DtSysParam;

        private DataTable GetSysParam()
        {
            if (DtSysParam == null)
            {
                try
                {
                    string strSql = "SELECT * FROM SYS_PARAM";
                    DtSysParam = ExecuteDataTable(strSql);
                }
                catch
                {
                    DtSysParam = new DataTable();//如果不存在这个表的话防止出错
                }
            }
            return DtSysParam;
        }

        /// <summary>
        /// 获得序列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetSysParamValue(string sysParamCode)
        {
            //系统参数的编号为四位的
            string sysParamValue = "";
            DataRow dr = GetSysParam().Select(string.Format("PARAMCODE='{0}' ", string.Format("{0:0000}", sysParamCode))).FirstOrDefault();
            if (dr != null)
            {
                sysParamValue = dr["PARAMVALUE"] + "";
            }
            return sysParamValue;
        }
        #endregion
        #region 获取序列
        private DataTable DtSeq;

        private DataTable GetDBSeq()
        {
            if (DtSeq == null)
            {
                if (_DbProviderType == DbProviderType.Oracle || _DbProviderType == DbProviderType.OracleDataAccess)
                {
                    string strSql = "SELECT * FROM USER_SEQUENCES";
                    DtSeq = ExecuteDataTable(strSql);
                }
                else
                {
                    DtSeq = new DataTable();
                }
            }
            return DtSeq;
        }

        /// <summary>
        /// 获得序列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public XElement GetSequenceName(string tableName)
        {
            XElement xeSequence = null;
            DataRow dr = GetDBSeq().Select(string.Format("SEQUENCE_NAME='{0}' ", "SEQ_" + tableName.ToUpper())).FirstOrDefault();
            if (dr != null)
            {
                //暂时只有ID是序列，先写死，如果有多个，以后增加表去维护
                xeSequence = new XElement("ID", dr["SEQUENCE_NAME"]);
            }
            return xeSequence;
        }
        #endregion
        public TwiCommand CreateCommand()
        {
            if (_DbProviderType == DbProviderType.SqlServer)
            {
                return new TwiCommandSql(this);
            }
            else if (_DbProviderType == DbProviderType.OracleDataAccess)
            {
                return new TwiCommandOracle(this);
            }
            else if (_DbProviderType == DbProviderType.Oracle)
            {
                return new TwiCommandOracle(this);
            }
            else
            {
                throw new Exception("不支持该数据库");
            }
        }
        public TwiCommand CreateCommand(DbCommand cmd)
        {
            if (cmd is SqlCommand)
            {
                return new TwiCommandSql(this, cmd);
            }
            else
            {
                return new TwiCommandOracle(this, cmd);
            }
        }

        public TwiCommand CreateCommand(DbConnection conn, DbTransaction txn)
        {
            if (_DbProviderType == DbProviderType.SqlServer)
            {
                return new TwiCommandSql(this, conn, txn);
            }
            else if (_DbProviderType == DbProviderType.OracleDataAccess)
            {
                return new TwiCommandOracle(this, conn, txn);
            }
            else if (_DbProviderType == DbProviderType.Oracle)
            {
                return new TwiCommandOracle(this, conn, txn);
            }
            else
            {
                throw new Exception("不支持该数据库");
            }
        }
        #endregion
    }

    /// <summary>
    /// 数据库类型枚举
    /// </summary>
    public enum DbProviderType : byte
    {
        SqlServer = 1,
        Oracle = 2, //System.Data.OracleClient   微软对Oracle访问
        MySql = 3,
        OracleDataAccess, //Oracle.DataAccess.Client   Oracle自带的访问
        SQLite,
        ODBC,
        OleDb,
        Firebird,
        PostgreSql,
        DB2,
        Informix,
        SqlServerCe
    }
    public enum TwiDbType
    {
        SqlBigInt = 0,
        SqlBit = 2,
        SqlNVarChar = 12,
        SqlVarChar = 22,
        OracleInt16 = 111,
        OracleInt32 = 112,
        OracleInt64 = 113,
        OracleNClob = 116,
        OracleNChar = 117,
        OracleNVarchar2 = 119,
        OracleVarchar2 = 126,
    }
    /// <summary>
    /// DbProviderFactory工厂类
    /// </summary>
    public class ProviderFactory
    {
        private static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>();
        private static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20);
        static ProviderFactory()
        {
            //加载已知的数据库访问类的程序集
            providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient");
            providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb");
            providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC");
            providerInvariantNames.Add(DbProviderType.Oracle, "System.Data.OracleClient");
            providerInvariantNames.Add(DbProviderType.OracleDataAccess, "Oracle.DataAccess.Client");
            providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient");
            providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite");
            providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird");
            providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql");
            providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries");
            providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix");
            providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe");
        }
        /// <summary>
        /// 获取指定数据库类型对应的程序集名称
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static string GetProviderInvariantName(DbProviderType providerType)
        {
            return providerInvariantNames[providerType];
        }
        /// <summary>
        /// 根据程序集名称获取数据库类型
        /// </summary>
        /// <param name="providerInvariantName"></param>
        /// <returns></returns>
        public static DbProviderType GetDbProviderType(string providerInvariantName)
        {
            //return providerInvariantNames.First(c => c.Value.Equals(providerInvariantNames)).Key;
            DbProviderType returnType = DbProviderType.SqlServer;
            foreach (var item in providerInvariantNames)
            {
                if (item.Value == providerInvariantName)
                {
                    returnType = item.Key;
                    break;
                }
            }
            return returnType;
        }

        /// <summary>
        /// 获取指定类型的数据库对应的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
        {
            //如果还没有加载，则加载该DbProviderFactory
            if (!providerFactoies.ContainsKey(providerType))
            {
                providerFactoies.Add(providerType, ImportDbProviderFactory(providerType));
            }
            return providerFactoies[providerType];
        }
        /// <summary>
        /// 加载指定数据库类型的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
        {
            string providerName = providerInvariantNames[providerType];
            DbProviderFactory factory = null;
            try
            {
                //从全局程序集中查找
                factory = DbProviderFactories.GetFactory(providerName);
            }
            catch //(ArgumentException e)
            {
                factory = null;
            }
            return factory;
        }
    }


    /// <summary>
    /// 实体阅读器类，可以从DataTable中或者DbDataReader的实例中将数据转换成对应的实例
    /// </summary>
    public sealed class EntityReader
    {
        private const BindingFlags BindingFlag = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
        //将类型与该类型所有的可写且未被忽略属性之间建立映射
        private static Dictionary<Type, Dictionary<string, PropertyInfo>> propertyMappings = new Dictionary<Type, Dictionary<string, PropertyInfo>>();

        /// <summary>
        /// 将DataTable中的所有数据转换成List>T<集合
        /// </summary>
        /// <typeparam name="T">DataTable中每条数据可以转换的数据类型</typeparam>
        /// <param name="dataTable">包含有可以转换成数据类型T的数据集合</param>
        /// <returns></returns>
        public static List<T> GetEntities<T>(DataTable dataTable) where T : new()
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException("dataTable");
            }
            //如果T的类型满足以下条件：字符串、ValueType或者是Nullable<ValueType>
            if (typeof(T) == typeof(string) || typeof(T) == typeof(byte[]) || typeof(T).IsValueType)
            {
                return GetSimpleEntities<T>(dataTable);
            }
            else
            {
                return GetComplexEntities<T>(dataTable);
            }
        }
        /// <summary>
        /// 将DbDataReader中的所有数据转换成List>T<集合
        /// </summary>
        /// <typeparam name="T">DbDataReader中每条数据可以转换的数据类型</typeparam>
        /// <param name="dataTable">包含有可以转换成数据类型T的DbDataReader实例</param>
        /// <returns></returns>
        public static List<T> GetEntities<T>(DbDataReader reader) where T : new()
        {
            List<T> list = new List<T>();
            if (reader == null)
            {
                throw new ArgumentNullException("reader");
            }
            //如果T的类型满足以下条件：字符串、ValueType或者是Nullable<ValueType>
            if (typeof(T) == typeof(string) || typeof(T).IsValueType)
            {
                return GetSimpleEntities<T>(reader);
            }
            else
            {
                return GetComplexEntities<T>(reader);
            }

        }
        /// <summary>
        /// 从DataTable中将每一行的第一列转换成T类型的数据
        /// </summary>
        /// <typeparam name="T">要转换的目标数据类型</typeparam>
        /// <param name="dataTable">包含有可以转换成数据类型T的数据集合</param>
        /// <returns></returns>
        private static List<T> GetSimpleEntities<T>(DataTable dataTable) where T : new()
        {
            List<T> list = new List<T>();
            foreach (DataRow row in dataTable.Rows)
            {
                list.Add((T)GetValueFromObject(row[0], typeof(T)));
            }
            return list;
        }
        /// <summary>
        /// 将指定的 Object 的值转换为指定类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <param name="targetType">要转换的目标数据类型</param>
        /// <returns></returns>
        private static object GetValueFromObject(object value, Type targetType)
        {
            if (targetType == typeof(string))//如果要将value转换成string类型
            {
                return GetString(value);
            }
            else if (targetType == typeof(byte[]))//如果要将value转换成byte[]类型
            {
                return GetBinary(value);
            }
            else if (targetType.IsGenericType)//如果目标类型是泛型
            {
                return GetGenericValueFromObject(value, targetType);
            }
            else//如果是基本数据类型（包括数值类型、枚举和Guid）
            {
                return GetNonGenericValueFromObject(value, targetType);
            }
        }

        /// <summary>
        /// 从DataTable中读取复杂数据类型集合
        /// </summary>
        /// <typeparam name="T">要转换的目标数据类型</typeparam>
        /// <param name="dataTable">包含有可以转换成数据类型T的数据集合</param>
        /// <returns></returns>
        private static List<T> GetComplexEntities<T>(DataTable dataTable) where T : new()
        {
            if (!propertyMappings.ContainsKey(typeof(T)))
            {
                GenerateTypePropertyMapping(typeof(T));
            }
            List<T> list = new List<T>();
            Dictionary<string, PropertyInfo> properties = propertyMappings[typeof(T)];
            T t;
            foreach (DataRow row in dataTable.Rows)
            {
                t = new T();
                foreach (KeyValuePair<string, PropertyInfo> item in properties)
                {
                    //如果对应的属性名出现在数据源的列中则获取值并设置给对应的属性
                    if (row[item.Key] != null)
                    {
                        item.Value.SetValue(t, GetValueFromObject(row[item.Key], item.Value.PropertyType), null);
                    }
                }
                list.Add(t);
            }
            return list;
        }

        /// <summary>
        /// 从DbDataReader的实例中读取复杂的数据类型
        /// </summary>
        /// <typeparam name="T">要转换的目标类</typeparam>
        /// <param name="reader">DbDataReader的实例</param>
        /// <returns></returns>
        private static List<T> GetComplexEntities<T>(DbDataReader reader) where T : new()
        {
            if (!propertyMappings.ContainsKey(typeof(T)))//检查当前是否已经有该类与类的可写属性之间的映射
            {
                GenerateTypePropertyMapping(typeof(T));
            }
            List<T> list = new List<T>();
            Dictionary<string, PropertyInfo> properties = propertyMappings[typeof(T)];
            T t;
            while (reader.Read())
            {
                t = new T();
                foreach (KeyValuePair<string, PropertyInfo> item in properties)
                {
                    //如果对应的属性名出现在数据源的列中则获取值并设置给对应的属性
                    if (reader[item.Key] != null)
                    {
                        item.Value.SetValue(t, GetValueFromObject(reader[item.Key], item.Value.PropertyType), null);
                    }
                }
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 从DbDataReader的实例中读取简单数据类型（String,ValueType)
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="reader">DbDataReader的实例</param>
        /// <returns></returns>
        private static List<T> GetSimpleEntities<T>(DbDataReader reader)
        {
            List<T> list = new List<T>();
            while (reader.Read())
            {
                list.Add((T)GetValueFromObject(reader[0], typeof(T)));
            }
            return list;
        }
        /// <summary>
        /// 将Object转换成字符串类型
        /// </summary>
        /// <param name="value">object类型的实例</param>
        /// <returns></returns>
        private static object GetString(object value)
        {
            return Convert.ToString(value);
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <param name="targetType"></param>
        /// <returns></returns>
        private static object GetEnum(object value, Type targetType)
        {
            return Enum.Parse(targetType, value.ToString());
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetBoolean(object value)
        {
            if (value is Boolean)
            {
                return value;
            }
            else
            {
                byte byteValue = (byte)GetByte(value);
                if (byteValue == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetByte(object value)
        {
            if (value is Byte)
            {
                return value;
            }
            else
            {
                return byte.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetSByte(object value)
        {
            if (value is SByte)
            {
                return value;
            }
            else
            {
                return SByte.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetChar(object value)
        {
            if (value is Char)
            {
                return value;
            }
            else
            {
                return Char.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetGuid(object value)
        {
            if (value is Guid)
            {
                return value;
            }
            else
            {
                return new Guid(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetInt16(object value)
        {
            if (value is Int16)
            {
                return value;
            }
            else
            {
                return Int16.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetUInt16(object value)
        {
            if (value is UInt16)
            {
                return value;
            }
            else
            {
                return UInt16.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetInt32(object value)
        {
            if (value is Int32)
            {
                return value;
            }
            else
            {
                return Int32.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetUInt32(object value)
        {
            if (value is UInt32)
            {
                return value;
            }
            else
            {
                return UInt32.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetInt64(object value)
        {
            if (value is Int64)
            {
                return value;
            }
            else
            {
                return Int64.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetUInt64(object value)
        {
            if (value is UInt64)
            {
                return value;
            }
            else
            {
                return UInt64.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetSingle(object value)
        {
            if (value is Single)
            {
                return value;
            }
            else
            {
                return Single.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetDouble(object value)
        {
            if (value == null || Convert.ToString(value).Length == 0)
            {
                return 0;
            }
            else if (value is Double)
            {
                return value;
            }
            else
            {
                return Double.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetDecimal(object value)
        {
            if (value == null || Convert.ToString(value).Length == 0)
            {
                return 0;
            }
            else if (value is Decimal)
            {
                return value;
            }
            else
            {
                return Decimal.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetDateTime(object value)
        {
            if (value is DateTime)
            {
                return value;
            }
            else
            {
                return DateTime.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static object GetTimeSpan(object value)
        {
            if (value is TimeSpan)
            {
                return value;
            }
            else
            {
                return TimeSpan.Parse(value.ToString());
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定枚举类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <returns></returns>
        private static byte[] GetBinary(object value)
        {
            //如果该字段为NULL则返回null
            if (value == DBNull.Value)
            {
                return null;
            }
            else if (value is Byte[])
            {
                return (byte[])(value);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 将Object类型数据转换成对应的可空数值类型表示
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <param name="targetType">可空数值类型</param>
        /// <returns></returns>
        private static object GetGenericValueFromObject(object value, Type targetType)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                //获取可空数值类型对应的基本数值类型，如int?->int,long?->long
                //Type nonGenericType = genericTypeMappings[targetType];
                Type nonGenericType = targetType.GetGenericArguments()[0];
                return GetNonGenericValueFromObject(value, nonGenericType);
            }
        }

        /// <summary>
        /// 将指定的 Object 的值转换为指定类型的值。
        /// </summary>
        /// <param name="value">实现 IConvertible 接口的 Object，或者为 null</param>
        /// <param name="targetType">目标对象的类型</param>
        /// <returns></returns>
        private static object GetNonGenericValueFromObject(object value, Type targetType)
        {
            if (targetType.IsEnum)//因为
            {
                return GetEnum(value, targetType);
            }
            else
            {
                switch (targetType.Name)
                {
                    case "Byte": return GetByte(value);
                    case "SByte": return GetSByte(value);
                    case "Char": return GetChar(value);
                    case "Boolean": return GetBoolean(value);
                    case "Guid": return GetGuid(value);
                    case "Int16": return GetInt16(value);
                    case "UInt16": return GetUInt16(value);
                    case "Int32": return GetInt32(value);
                    case "UInt32": return GetUInt32(value);
                    case "Int64": return GetInt64(value);
                    case "UInt64": return GetUInt64(value);
                    case "Single": return GetSingle(value);
                    case "Double": return GetDouble(value);
                    case "Decimal": return GetDecimal(value);
                    case "DateTime": return GetDateTime(value);
                    case "TimeSpan": return GetTimeSpan(value);
                    default: return null;
                }
            }
        }

        /// <summary>
        /// 获取该类型中属性与数据库字段的对应关系映射
        /// </summary>
        /// <param name="type"></param>
        private static void GenerateTypePropertyMapping(Type type)
        {
            if (type != null)
            {
                PropertyInfo[] properties = type.GetProperties(BindingFlag);
                Dictionary<string, PropertyInfo> propertyColumnMapping = new Dictionary<string, PropertyInfo>(properties.Length);
                string description = string.Empty;
                Attribute[] attibutes = null;
                string columnName = string.Empty;
                foreach (PropertyInfo p in properties)
                {
                    columnName = string.Empty;
                    attibutes = Attribute.GetCustomAttributes(p);
                    foreach (Attribute attribute in attibutes)
                    {
                        //检查是否设置了ColumnName属性
                        if (attribute.GetType() == typeof(ColumnNameAttribute))
                        {
                            columnName = ((ColumnNameAttribute)attribute).ColumnName;
                            break;
                        }
                    }
                    //如果该属性是可读并且未被忽略的，则有可能在实例化该属性对应的类时用得上
                    if (p.CanWrite)
                    {
                        //如果没有设置ColumnName属性，则直接将该属性名作为数据库字段的映射
                        if (string.IsNullOrEmpty(columnName))
                        {
                            columnName = p.Name;
                        }
                        propertyColumnMapping.Add(columnName, p);
                    }
                }
                propertyMappings.Add(type, propertyColumnMapping);
            }
        }

    }

    /// <summary>
    /// 自定义属性，用于指示如何从DataTable或者DbDataReader中读取类的属性值
    /// </summary>
    public class ColumnNameAttribute : Attribute
    {
        /// <summary>
        /// 类属性对应的列名
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="columnName">类属性对应的列名</param>
        public ColumnNameAttribute(string columnName)
        {
            ColumnName = columnName;
        }
    }
    public static class SqlExtension
    {
        public static SqlParameter ToSqlParameter(this XElement me, string sqlDbType)
        {
            switch (sqlDbType)
            {
                case "uniqueidentifier":
                    return me.ToSqlParameter<Guid>();
                case "nvarchar":
                    return me.ToSqlParameter<string>();
                case "ntext":
                    return me.ToSqlParameter<string>();
                case "text":
                    return me.ToSqlParameter<string>();
                case "varchar":
                    goto case "nvarchar";
                case "int":
                    return me.ToSqlParameter<int>();
                case "bigint":
                    return me.ToSqlParameter<long>();
                case "datetime":
                    return me.ToSqlParameter<DateTime>();
                case "decimal":
                    return me.ToSqlParameter<decimal>();
                case "float":
                    return me.ToSqlParameter<float>();
                case "double":
                    return me.ToSqlParameter<double>();
                case "money":
                    return me.ToSqlParameter<decimal>();
                case "xml":
                    return me.ToSqlParameter<XElement>();

            }
            return null;
        }
        public static void SetParameter(this SqlCommand me, SqlParameter para)
        {
            if (para == null) { return; }
            if (me.Parameters.Contains(para.ParameterName))
            {
                me.Parameters[para.ParameterName].Value = para.Value;
            }
            else
            {
                me.Parameters.Add(para);
            }
        }

        public static void SetParameters(this SqlCommand me, SqlParameter[] parameters)
        {
            foreach (var x in parameters)
            {
                me.SetParameter(((ICloneable)x).Clone() as SqlParameter);
            }
        }

        public static XElement ToXElement(this DbDataReader rdr, DbCommand cmd, int i, bool isXml = false)
        {
            string name = rdr.GetName(i);
            if (rdr.GetValue(i) == DBNull.Value)
            {
                XElement xe = new XElement(name);
                return xe;
            }
            else if (rdr.GetDataTypeName(i).ToUpper() == "RAW")
            {
                XElement xe = new XElement(name);
                xe.SetValue(new Guid(rdr.GetValue(i) as byte[]));
                return xe;
            }
            else
            {
                XElement xe = new XElement(name);
                xe.SetValue(rdr.GetValue(i).ToString().ReplaceLowOrderASCIICharacters());
                return xe;
            }
        }
        public static string ReplaceLowOrderASCIICharacters(this string tmp)
        {
            StringBuilder info = new StringBuilder();
            foreach (char cc in tmp)
            {
                int ss = (int)cc;
                if (((ss >= 0) && (ss <= 8)) || ((ss >= 11) && (ss <= 12)) || ((ss >= 14) && (ss <= 32)))
                    info.AppendFormat(" ", ss);
                else info.Append(cc);
            }
            return info.ToString();
        }
        #region XML To Parameter

        #region SqlParameter
        /// <summary>
        /// SqlParameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="me"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static SqlParameter ToSqlParameter<T>(this XElement me, string parameterName = "")
        {
            if (me == null)
            {
                return null;
            }
            if (parameterName == "")
            {
                parameterName = me.Name.LocalName;
            }
            //特殊字段的处理
            if (me.Name.LocalName == "SortOrder" && me.Value.ToString().Trim() == "")
            {
                SqlParameter p = new SqlParameter("@SortOrder", SqlDbType.Int);
                p.Value = 0;
                return p;
            }

            if (typeof(T) == typeof(XElement))
            {
                SqlParameter p = new SqlParameter("@" + parameterName, SqlDbType.Xml);
                p.Value = me.HasElements ? (object)new System.Xml.XmlTextReader(me.Elements().Select(x => x.ToString()).Aggregate((x, y) => x + y), System.Xml.XmlNodeType.Element, null)
                    : DBNull.Value;
                return p;
            }
            string value = me.Value.Trim();
            if (me.HasElements)
            {
                if (me.Element("NewValue") == null)
                {
                    value = me.ToString();
                }
                else
                {
                    value = me.Element("NewValue").Value;
                }
            }
            if (typeof(T) == typeof(string))
            {
                return new SqlParameter("@" + parameterName, value);
            }
            else if (typeof(T) == typeof(DateTime))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : DateTime.Parse(value));
            }
            else if (typeof(T) == typeof(Guid))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : new Guid(value));
            }
            else if (typeof(T) == typeof(decimal))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : decimal.Parse(value));
            }
            else if (typeof(T) == typeof(float))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : float.Parse(value));
            }
            else if (typeof(T) == typeof(double))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : double.Parse(value));
            }
            else if (typeof(T) == typeof(int))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : int.Parse(value));
            }
            else if (typeof(T) == typeof(long))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : long.Parse(value));
            }

            return null;
        }
        #endregion

        #region OracleParameter
        /// <summary>
        /// OracleParameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="me"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static Oracle.DataAccess.Client.OracleParameter ToOracleParameter<T>(this XElement me, string parameterName = "")
        {
            if (me == null)
            {
                return null;
            }
            if (parameterName == "")
            {
                parameterName = me.Name.LocalName;
            }
            //特殊字段的处理
            if (me.Name.LocalName == "SortOrder" && me.Value.ToString().Trim() == "")
            {
                Oracle.DataAccess.Client.OracleParameter p = new Oracle.DataAccess.Client.OracleParameter("SortOrder", SqlDbType.Int);
                p.Value = 0;
                return p;
            }

            if (typeof(T) == typeof(XElement))
            {
                Oracle.DataAccess.Client.OracleParameter p = new Oracle.DataAccess.Client.OracleParameter(parameterName, SqlDbType.Xml);
                p.Value = me.HasElements ? (object)new System.Xml.XmlTextReader(me.Elements().Select(x => x.ToString()).Aggregate((x, y) => x + y), System.Xml.XmlNodeType.Element, null)
                    : DBNull.Value;
                return p;
            }

            string value = me.Element("NewValue") == null ? (me.Value) : me.Element("NewValue").Value;
            if (typeof(T) == typeof(string))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, value);
            }
            else if (typeof(T) == typeof(DateTime))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : DateTime.Parse(value));
            }
            else if (typeof(T) == typeof(Guid))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : new Guid(value).ToByteArray());
            }
            else if (typeof(T) == typeof(decimal))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : decimal.Parse(value));
            }
            else if (typeof(T) == typeof(float))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : float.Parse(value));
            }
            else if (typeof(T) == typeof(double))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : double.Parse(value));
            }
            else if (typeof(T) == typeof(int))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : int.Parse(value));
            }
            else if (typeof(T) == typeof(long))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : long.Parse(value));
            }

            return null;
        }
        #endregion

        #region SetOracleParameter

        public static void SetOracleParameter(this Oracle.DataAccess.Client.OracleCommand me, string paraName, object paraValue)
        {
            if (paraValue is XElement)
            {
                paraValue = new Oracle.DataAccess.Types.OracleXmlType(me.Connection, new System.Xml.XmlTextReader(paraValue.ToString(), System.Xml.XmlNodeType.Document, null));
            }
            else if (paraValue is IEnumerable<XElement>)
            {
                paraValue = (paraValue as IEnumerable<XElement>).Select(x => x.ToString()).Aggregate((x, y) => x + y);
            }

            if (me.Parameters.Contains(paraName))
            {
                me.Parameters[paraName].Value = paraValue;
            }
            else
            {
                var para = new Oracle.DataAccess.Client.OracleParameter(paraName, paraValue);
                if (paraValue is XElement)
                {
                    para.DbType = DbType.Xml;
                }
                me.Parameters.Add(para);
            }
        }

        public static void SetOracleParameter(this Oracle.DataAccess.Client.OracleCommand me, Oracle.DataAccess.Client.OracleParameter para)
        {
            if (para == null) { return; }
            if (me.Parameters.Contains(para.ParameterName))
            {
                me.Parameters[para.ParameterName].Value = para.Value;
            }
            else
            {
                me.Parameters.Add(para);
            }
        }

        public static void SetOracleParameters(this Oracle.DataAccess.Client.OracleCommand me, Oracle.DataAccess.Client.OracleParameter[] parameters)
        {
            foreach (var x in parameters)
            {
                me.SetOracleParameter(((ICloneable)x).Clone() as Oracle.DataAccess.Client.OracleParameter);
            }
        }

        #endregion

        #region 准备执行语句前准备抽取OracleCommand参数
        /// <summary>
        /// 准备执行语句前准备抽取OracleCommand参数
        /// </summary>
        /// <param name="me"></param>
        /// <param name="maximumOrclParameters"></param>
        public static void PrepareOracleParameters(this Oracle.DataAccess.Client.OracleCommand me, List<Oracle.DataAccess.Client.OracleParameter> maximumOrclParameters)
        {
            var paramNameList = me.CommandText.GetParameterNames();
            me.Parameters.Clear();
            foreach (string paramName in paramNameList)
            {
                var p = maximumOrclParameters.SingleOrDefault(x => x.ParameterName.ToUpper() == paramName.ToUpper());
                if (p != null)
                {
                    p = (Oracle.DataAccess.Client.OracleParameter)p.Clone();
                    p.ParameterName = p.ParameterName + me.Parameters.Count.ToString("000");
                    me.Parameters.Add(p);
                }
            }
            //写日志
            TwiDAHelper.WriteLog(me);
        }
        #endregion
        public static List<string> GetParameterNames(this string sql)
        {
            sql = sql + " "; //防止该语句已参数结尾
            List<string> retList = new List<string>();
            bool hasUnmatchedSingleQuotation = false; //是否存在未配对的单引号
            int curParamStartAt = -1;//当前参数在sql中的开始位置，即冒号后一位
            int curPos = 1;
            do
            {
                char curChar = sql[curPos];
                curPos++;
                if (curChar == '\'')
                {
                    hasUnmatchedSingleQuotation = !hasUnmatchedSingleQuotation;
                    continue;
                }
                if (!hasUnmatchedSingleQuotation)//防止在字符窜里面仍去判断参数
                {
                    if (curChar == ':')
                    {
                        curParamStartAt = curPos;
                    }
                    else if (curParamStartAt != -1 &&
                        (curChar < 'a' || curChar > 'z') && (curChar < 'A' || curChar > 'Z') && (curChar < '0' || curChar > '9') && curChar != '_')
                    {
                        string paramName = sql.Substring(curParamStartAt, curPos - curParamStartAt - 1).ToUpper();
                        retList.Add(paramName);
                        curParamStartAt = -1;
                    }
                }
            }
            while (curPos < sql.Length);
            return retList;
        }
        #endregion
    }
}
